3. Keys

What is a Primary Key?

A primary key is a column (or set of columns) that uniquely identifies each row in a table.
It cannot be NULL and must be unique.
Example:

CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100)
);

Here, student_id is the primary key.
It makes sure each student has a unique ID — no duplicates, no NULLs.

What is a Foreign Key?

A foreign key is a column in one table that links to the primary key of another table.
It creates a relationship between two tables.

📌 Example:

CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE StudentCourse (
    student_id INT,
    course_id INT,
    FOREIGN KEY (student_id) REFERENCES Students(student_id)
);

student_id in StudentCourse is a foreign key.
It ensures that student_id in StudentCourse must exist in the Students table.

What is a Composite Key?

A composite key is made up of two or more columns that together uniquely identify a row.
No single column in the combination is unique on its own.
📌 Example:

CREATE TABLE StudentCourse (
    student_id INT,
    course_id INT,
    grade CHAR(1),
    PRIMARY KEY (student_id, course_id)
);

Here, (student_id, course_id) is the composite key.
This means a student can take many courses, and a course can have many students, but the pair must be unique.

🔎 Why Do We Need a Composite Key? (With Simple Example)

Imagine this scenario:
You’re tracking which students enrolled in which courses — each student can take many courses, and each course has many students.
Table: StudentCourse

student_id	course_id	grade
101	501	A
101	502	B
102	501	A

Now:

student_id alone is not unique — because student 101 appears twice.
course_id alone is not unique — course 501 appears twice.
But the pair (student_id, course_id) is unique — no student can enroll in the same course twice.
So Why Use Composite Key?
Without it:
You could accidentally enter the same student-course combo multiple times (duplicate data).
Data becomes inconsistent, and grade tracking breaks.
With composite key (student_id, course_id):
You guarantee that each student-course pair appears only once — maintaining data integrity.

example foregin key

mysql> select * from students;
+----+---------+------+----------+
| id | name    | age  | division |
+----+---------+------+----------+
|  1 | sanskar |   13 | A        |
|  2 | jack    |   19 | C        |
|  3 | james   |   19 | C        |
|  4 | jhon    |   19 | D        |
|  5 | ritik   |   18 | B        |
|  6 | rishav  |   17 | A        |
+----+---------+------+----------+
6 rows in set (0.04 sec)

mysql> create table stud_marks(id int(10) primary key,math int(5),science int(5),foreign key (id) references students(id));
Query OK, 0 rows affected, 3 warnings (0.46 sec)

mysql> select * from stud_marks;
Empty set (0.01 sec)

mysql> insert into stud_marks(math,science) values(50,67);
ERROR 1364 (HY000): Field 'id' doesn't have a default value
mysql> insert into stud_marks(id,math,science) values(80,50,67);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`practice`.`stud_marks`, CONSTRAINT `stud_marks_ibfk_1` FOREIGN KEY (`id`) REFERENCES `students` (`id`))
mysql> insert into stud_marks(id,math,science) values(4,50,67);
Query OK, 1 row affected (0.01 sec)

mysql> select * from stud_marks;
+----+------+---------+
| id | math | science |
+----+------+---------+
|  4 |   50 |      67 |
+----+------+---------+

Advanced Key-Based Interview Questions

1. In a table with a composite primary key, can one column be NULL? Why or why not?

Answer:
No. All columns in a composite primary key must be NOT NULL, because the key must uniquely and completely identify each row — NULL would violate uniqueness and completeness.

2. How would you handle a foreign key that references a composite key in another table?

Answer:
You must create a foreign key that includes both columns matching the composite key in order and data type.

-- Parent table
CREATE TABLE Orders (
    order_id INT,
    product_id INT,
    PRIMARY KEY (order_id, product_id)
);

-- Child table
CREATE TABLE Shipments (
    order_id INT,
    product_id INT,
    FOREIGN KEY (order_id, product_id) REFERENCES Orders(order_id, product_id)
);

3. Can a table have multiple foreign keys referencing the same table but different keys? How would you design this?

Answer:
Yes. Example: a Transaction table with both sender_id and receiver_id referencing the Users table.

CREATE TABLE Transactions (
    id INT PRIMARY KEY,
    sender_id INT,
    receiver_id INT,
    FOREIGN KEY (sender_id) REFERENCES Users(user_id),
    FOREIGN KEY (receiver_id) REFERENCES Users(user_id)
);

4. In what scenarios would you avoid using composite keys and use surrogate keys instead?

Answer:
When:

Composite keys become large and complex (bad for indexing & joins).
Relationships are many-to-many but frequently queried.
Need to decouple business logic from identifiers.

Surrogate key example:

CREATE TABLE StudentCourse (
    id SERIAL PRIMARY KEY,
    student_id INT,
    course_id INT
);

5. How does a composite key affect performance in large-scale joins?

Answer:
Negatively — composite keys:

Increase index size and lookup cost.
Slow down joins due to multiple column comparisons.
Use surrogate keys for faster performance in large joins.

Unique key

primary key identifies each record uniquely in the table where there can be no null or double records also in a table there can be only one primary key
wheras unique key is also a unique identfier of the column but the values can be zero and there can be multiple unique key columns

candidate key

a column or set of columns that can qualify as unique identifier
a table has multiple candidate key but only one is choosen as primary key

alternate key

a candidate key which is not choosen as primary key still unique and can be used